package cn.bdqn.dao.Imp;

import cn.bdqn.dao.Usersdao;
import cn.bdqn.entity.Users;
import cn.bdqn.utils.Baseutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.util.Date;
import java.util.List;

/**
 * @author 梁非凡
 * @date 2018/8/13 16:32
 */
public class UserdaoImp implements Usersdao {

    @Override
    public Users findbyidandname(String name,String password)throws Exception {
        String sql="SELECT u.*,t.name as typename  FROM users as u INNER JOIN user_type as t on u.typeid=t.id\n" +
                "WHERE u.name=? and u.password=?";
        QueryRunner qr=new QueryRunner();
        Users users = qr.query(Baseutils.getconn(),sql, new BeanHandler<Users>(Users.class), name, password);
        return users;
    }

    @Override
    public Integer amend(String pwd, Integer id) throws Exception {
        String sql="UPDATE users SET password=? ,lastModifyTime=? WHERE id=?";
        QueryRunner qr=new QueryRunner();
        int i = qr.update(Baseutils.getconn(),sql, pwd,new Date(), id);
        return i;
    }

    @Override
    public Integer logout(Users users) throws Exception {
        String sql="UPDATE users SET staus=? WHERE id=?";
        int i = Baseutils.getQueryRunner().update(sql, 0, users.getId());
        return i;
    }

    @Override
    public Integer findAll(String name) throws Exception {
        Long count=0L;
        if (name!=null&&!"".equals(name.trim())){
            String sql="SELECT COUNT(1) FROM users WHERE typeid=2 and name like ?";
            count=Baseutils.getQueryRunner().query(sql,new ScalarHandler<>(),"%"+name+"%");
        }else {
            String sql="SELECT COUNT(1) FROM users  WHERE typeid=2 ";
            count= Baseutils.getQueryRunner().query(sql,new ScalarHandler<>());
        }
        return Integer.parseInt(count.toString());
    }

    @Override
    public List<Users> findpageByname(String name, Integer startIndex, Integer pageSize) throws Exception {
        List<Users> list=null;
        if (name!=null&&!"".equals(name.trim())){
            String sql="SELECT u.*,t.name as typename FROM users as u INNER JOIN user_type as t on u.typeid=t.id WHERE u.typeid=2 and u.name LIKE ? ORDER BY lastModifyTime LIMIT ?,?";
            list=  Baseutils.getQueryRunner().query(sql,new BeanListHandler<Users>(Users.class),"%"+name+"%",startIndex,pageSize);
            for (Users users:list) {

                if (users.getStaus() == 1) {
                    users.setTypestaus("正常");
                }else {
                    users.setTypestaus("冻结");
                }
            }
        }else {
            String sql="SELECT u.*,t.name as typename  FROM users as u INNER JOIN user_type as t on u.typeid=t.id WHERE u.typeid=2 ORDER BY lastModifyTime LIMIT ?,?";
            list=  Baseutils.getQueryRunner().query(sql,new BeanListHandler<Users>(Users.class),startIndex,pageSize);
            for (Users users:list) {

                if (users.getStaus() == 1) {
                    users.setTypestaus("正常");
                }else {
                    users.setTypestaus("冻结");
                }
            }
        }
        return list;
    }
}
